USE [Exp]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 08/23/2020 18:13:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.Voucher.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, dbo.SessionInfo.SessionFrom, 
                      dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.ReadOnly, dbo.VoucherDetails.VoucherDetailID, 
                      dbo.VoucherDetails.AccountNo, Accounts_5.AccountTitle, dbo.VoucherDetails.Remarks, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, 
                      dbo.VoucherDetails.DrOrCr, Accounts_5.ParentAccount, dbo.Voucher.BankAccountID, dbo.Voucher.LoginID, dbo.Employees.EmployeeName, dbo.Voucher.HostName, 
                      dbo.Voucher.EntryDateTime, Accounts_2.AccountTitle AS BankAccountTitle, dbo.VoucherDetails.Amount AS ActualAmount, dbo.Voucher.ModifyID, 
                      Employees_1.EmployeeName AS ModifyName, dbo.Voucher.ModifyHostName, dbo.Voucher.ModifyDateTime, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, 
                      dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Rate, dbo.vw_Items.GroupID, 
                      dbo.vw_Items.GroupName, dbo.Voucher.EFormNo, dbo.Voucher.ReturnDate, dbo.Voucher.AgentID, Accounts_1.AccountTitle AS AgentName, dbo.Voucher.PartyID, 
                      Accounts_3.AccountTitle AS PartyName, Accounts_3.CellNo AS PartyCellNo, Accounts_3.Phone AS PartyPhoneNo, Accounts_3.Remarks AS PartyAddress, 
                      dbo.Voucher.BLDate, dbo.Voucher.BLNo, dbo.Voucher.ContainerTypeID, Groups_2.GroupName AS ContainerType, Groups_2.GroupNameUrdu AS ContainerTypeUrdu, 
                      dbo.Voucher.NoOfContainers, dbo.Voucher.ShipID, Groups_1.GroupName AS ShipName, Groups_1.GroupNameUrdu AS ShipNameUrdu, 
                      dbo.VoucherDetails.ShipMarkID, dbo.Groups.GroupName AS ShipMark, dbo.VoucherDetails.PackageID, Groups_3.GroupName AS Packages, dbo.VoucherDetails.Scale, 
                      dbo.VoucherDetails.ProductRemarks, dbo.VoucherDetails.NetWeight, dbo.VoucherDetails.GrossWeight, dbo.VoucherDetails.GrossWeightTotal, 
                      dbo.VoucherDetails.RateRemarks, dbo.Voucher.SupplierID, dbo.Accounts.AccountTitle AS SupplierName, dbo.Accounts.CellNo AS SupplierCellNo, 
                      dbo.Accounts.Phone AS SupplierPhoneNo, dbo.Accounts.Remarks AS SupplierAddress, dbo.Voucher.TransporterID, Accounts_4.AccountTitle AS TransporterName, 
                      Accounts_4.CellNo AS TransporterCellNo, Accounts_4.Phone AS TransporterPhoneNo, Accounts_4.Remarks AS TransporterAddress, dbo.Voucher.VoyNo, 
                      dbo.VoucherDetails.Type, dbo.VoucherDetails.FreightType, dbo.VoucherDetails.SaleDate, ISNULL(Accounts_5.Currency, 'PKR') AS Currency, dbo.Voucher.ContainerNo, 
                      dbo.Voucher.MiscAccountID, dbo.Voucher.NetInvoice, dbo.VoucherDetails.QTYSale, dbo.VoucherDetails.QTYDamage, dbo.Voucher.ShipLineID, 
                      Groups_4.GroupName AS ShipLine, Groups_4.GroupNameUrdu AS ShipLineUrdu, Accounts_3.AreaID, dbo.Area.AreaName, dbo.Area.AreaNameUrdu, 
                      dbo.Voucher.SubPartyID, ISNULL(Accounts_6.AccountTitle, '.') AS SubPartyName, dbo.Voucher.GD, dbo.VoucherDetails.QTYLoad
FROM         dbo.Accounts AS Accounts_1 RIGHT OUTER JOIN
                      dbo.Area RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_3 ON dbo.Area.AreaID = Accounts_3.AreaID RIGHT OUTER JOIN
                      dbo.Groups AS Groups_2 RIGHT OUTER JOIN
                      dbo.Groups AS Groups_1 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_4 RIGHT OUTER JOIN
                      dbo.Groups AS Groups_4 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_6 RIGHT OUTER JOIN
                      dbo.Voucher ON Accounts_6.AccountNo = dbo.Voucher.SubPartyID ON Groups_4.GroupID = dbo.Voucher.ShipLineID ON 
                      Accounts_4.AccountNo = dbo.Voucher.TransporterID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.Voucher.SupplierID = dbo.Accounts.AccountNo ON Groups_1.GroupID = dbo.Voucher.ShipID ON 
                      Groups_2.GroupID = dbo.Voucher.ContainerTypeID ON Accounts_3.AccountNo = dbo.Voucher.PartyID ON 
                      Accounts_1.AccountNo = dbo.Voucher.AgentID LEFT OUTER JOIN
                      dbo.Groups RIGHT OUTER JOIN
                      dbo.VoucherDetails LEFT OUTER JOIN
                      dbo.Groups AS Groups_3 ON dbo.VoucherDetails.PackageID = Groups_3.GroupID ON dbo.Groups.GroupID = dbo.VoucherDetails.ShipMarkID ON 
                      dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.VoucherDetails.ItemID = dbo.vw_Items.ItemID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_5 ON dbo.VoucherDetails.AccountNo = Accounts_5.AccountNo LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_2 ON dbo.Voucher.BankAccountID = Accounts_2.AccountNo LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 ON dbo.Voucher.ModifyID = Employees_1.EmployeeID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID
UNION ALL
SELECT     dbo.Invoice_SSP.Inv_SSPID, dbo.Invoice_SSP.Inv_SSPDate, dbo.Invoice_SSP.Inv_SSPVNo, dbo.Invoice_SSP.SessionID, SessionInfo_1.SessionTitle, 
                      SessionInfo_1.SessionFrom, SessionInfo_1.SessionTo, SessionInfo_1.CurrentSession, dbo.Invoice_SSP.Remarks AS MasterRemarks, dbo.Invoice_SSP.ReadOnly, 
                      dbo.Invoice_SSParty.inv_SSPartyID, dbo.Invoice_SSParty.DebitID, Accounts_1.AccountTitle, dbo.Invoice_SSParty.Remarks, dbo.Invoice_SSParty.NetSale, 
                      dbo.Invoice_SSParty.NetSale AS Debit, 0 AS Credit, 'Dr' AS DrOrCr, Accounts_1.ParentAccount, NULL AS BankAccID, dbo.Invoice_SSP.LoginID, 
                      Employees_2.EmployeeName, dbo.Invoice_SSP.HostName, dbo.Invoice_SSP.EntryDateTime, NULL AS BankAcTitle, dbo.Invoice_SSParty.NetSale AS ActualAmount, 
                      dbo.Invoice_SSP.ModifyID, Employees_1.EmployeeName AS ModifEmployeeName, dbo.Invoice_SSP.ModifyHostName, dbo.Invoice_SSP.ModifyDateTime, 
                      dbo.Invoice_SSParty.ItemID, dbo.Items.ItemCode, dbo.Items.ItemName, dbo.Items.ItemNameUrdu, dbo.Items.InUnit, dbo.Invoice_SSParty.TQTY, 0 AS Rate, 
                      dbo.Items.GroupID, Groups_6.GroupName, NULL AS EFormNo, NULL AS ReturnDate, NULL AS AgentID, NULL AS AgentName, dbo.Invoice_SSParty.DebitID AS PartyID, 
                      Accounts_1.AccountTitle AS PartyName, Accounts_1.CellNo AS PartyCellNo, Accounts_1.Phone AS PartyPhoneNo, Accounts_1.Remarks AS PartyAddress, 
                      dbo.Invoice_SSP.BEDate, dbo.Invoice_SSP.BENo, NULL AS ContainerTypeID, NULL AS ContainerType, NULL AS ContainerTypeUrdu, 
                      dbo.Invoice_SSP.NoOfContainers, NULL AS ShipID, NULL AS ShipName, NULL AS ShipNameUrdu, NULL AS ShipMarkID, NULL AS ShipMark, 
                      dbo.Invoice_SSParty.VarietyID, dbo.Varietys.VarietyName, 0 AS Scale, NULL AS ProductRemarks, 0 AS NetWeight, 0 AS GrossWeight, 0 AS GrossWeightTotal, NULL 
                      AS RateRemarks, NULL AS SupplierID, NULL AS SupplierName, NULL AS SupplierCellNo, NULL AS SupplierPhoneNo, NULL AS SupplierAddress, NULL 
                      AS TransID, NULL AS TransName, NULL AS TransCellNo, NULL AS TransPhoneNo, NULL AS TransAddress, NULL AS VoyNo, dbo.Invoice_SSParty.Type, 
                      0 AS FreightType, dbo.Invoice_SSParty.SaleDate, dbo.Invoice_SSP.Currency, dbo.Invoice_SSP.ContainerNo, NULL AS MiscAcID, dbo.Invoice_SSP.SumNetSale, 
                      dbo.Invoice_SSParty.SQTY, dbo.Invoice_SSParty.DQTY, NULL AS ShipLineID, NULL AS ShipLine, NULL AS ShipLineUrdu, Accounts_1.AreaID, Area_1.AreaName, 
                      Area_1.AreaNameUrdu, dbo.Invoice_SSParty.SubID, Accounts_6.AccountTitle AS SubName, dbo.Invoice_SSParty.ContainerNo AS GD, dbo.Invoice_SSParty.LQTY
FROM         dbo.Accounts AS Accounts_6 RIGHT OUTER JOIN
                      dbo.Items LEFT OUTER JOIN
                      dbo.Groups AS Groups_6 ON dbo.Items.GroupID = Groups_6.GroupID RIGHT OUTER JOIN
                      dbo.Invoice_SSParty LEFT OUTER JOIN
                      dbo.Varietys ON dbo.Invoice_SSParty.VarietyID = dbo.Varietys.VarietyID ON dbo.Items.ItemID = dbo.Invoice_SSParty.ItemID ON 
                      Accounts_6.AccountNo = dbo.Invoice_SSParty.SubID LEFT OUTER JOIN
                      dbo.Area AS Area_1 RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON Area_1.AreaID = Accounts_1.AreaID ON dbo.Invoice_SSParty.DebitID = Accounts_1.AccountNo RIGHT OUTER JOIN
                      dbo.Employees AS Employees_1 RIGHT OUTER JOIN
                      dbo.Invoice_SSP ON Employees_1.EmployeeCode = dbo.Invoice_SSP.ModifyID LEFT OUTER JOIN
                      dbo.Employees AS Employees_2 ON dbo.Invoice_SSP.LoginID = Employees_2.EmployeeCode ON 
                      dbo.Invoice_SSParty.inv_SSPID = dbo.Invoice_SSP.Inv_SSPID LEFT OUTER JOIN
                      dbo.SessionInfo AS SessionInfo_1 ON dbo.Invoice_SSP.SessionID = SessionInfo_1.SessionID